﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;
using MySkiPatrolSQL;
using System.Data;

namespace MySkiPatrolDAL
{
    public class MembershipDAO
    {
        SqlConnector SQLCONN = new SqlConnector();

        public WebAccess Login(WebAccess data)
        {
            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.Login(data), "data");
                DataTable dt = ds.Tables["data"];

                if (dt.Rows.Count == 1)
                {
                    data.MemberID = DataConvertor.toString(dt.Rows[0], "MemberID");
                    data.Password = DataConvertor.toString(dt.Rows[0], "Password");
                    data.DataTransaction.Success = true;
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if(data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11000", "An error occurred in your login attempt"));
            }

            return data;
        }

        public Member Logout(Member data)
        {
            return data;
        }

        public Patrol GetMembers(Patrol data)
        {
            List<Member> myReturn = new List<Member>();
            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMembers(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    Member item = new Member();
                    item.DataTransaction = new DataTransaction();

                    item.ID = DataConvertor.toString(dr, "MemberID");
                    item.Password = DataConvertor.toString(dr, "Password");
                    item.FirstName = DataConvertor.toTitleCase(dr, "FirstName");
                    item.LastName = DataConvertor.toTitleCase(dr, "LastName");
                    item.NSPNumber = DataConvertor.toString(dr, "NSPNumber");
                    item.Username = DataConvertor.toString(dr, "Username");
                    item.FullName = item.FirstName + " " + item.LastName;
                    item.DateDeleted = DataConvertor.toDateTimeOffset(dr, "DateDeleted");
                    if (item.DateDeleted != DateTimeOffset.MinValue) item.IsDeleted = true;
                    
                    item.DataTransaction.Success = true;
                    item.DataTransaction.PatrolPrefix = data.DataTransaction.PatrolPrefix;

                    myReturn.Add(item);
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11000", "An error occurred in your login attempt"));
            }

            data.Members = myReturn;
            return data;
        }

        public Member GetMember(Member data)
        {
            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMember(data), "data");
                DataTable dt = ds.Tables["data"];

                if (dt.Rows.Count == 1)
                {
                    data.ID = DataConvertor.toString(dt.Rows[0], "MemberID");
                    data.Password = DataConvertor.toString(dt.Rows[0], "Password");
                    data.FirstName = DataConvertor.toTitleCase(dt.Rows[0], "FirstName");
                    data.LastName = DataConvertor.toTitleCase(dt.Rows[0], "LastName");
                    data.NSPNumber = DataConvertor.toString(dt.Rows[0], "NSPNumber");
                    data.Username = DataConvertor.toString(dt.Rows[0], "Username");
                    data.FullName = data.FirstName + " " + data.LastName;
                    data.DateDeleted = DataConvertor.toDateTimeOffset(dt.Rows[0], "DateDeleted");
                    if (data.DateDeleted != DateTimeOffset.MinValue) data.IsDeleted = true;

                    data.DataTransaction.Success = true;
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11000", "An error occurred in your login attempt"));
            }

            return data;
        }

        public Member DeleteMember(Member data)
        {
            if(data.DataTransaction==null) data.DataTransaction=new DataTransaction();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.DeleteMember(data), "data");
                DataTable dt = ds.Tables["data"];

                //great...now check to see if it was deleted
                ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMember(data), "data");
                dt = ds.Tables["data"];

                if (dt.Rows.Count == 0)
                {
                    data.DataTransaction.Success = true;
                }
                else
                {
                    data.DataTransaction.Success = false;
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("00000", ex.Message));
            }
            return data;
        }

        public Member AddMember(Member data)
        {
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.AddMember(data), "data");
                DataTable dt = ds.Tables["data"];

                //great...now check to see if it was deleted
                ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMember(data), "data");
                dt = ds.Tables["data"];

                if (dt.Rows.Count == 1)
                {
                    data.DataTransaction.Success = true;
                }
                else
                {
                    data.DataTransaction.Success = false;
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("00000", ex.Message));
            }
            return data;
        }

        public Member UpdateMember(Member data)
        {
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.UpdateMember(data), "data");
                DataTable dt = ds.Tables["data"];

                //great...now check to see if it was deleted
                ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMember(data), "data");
                dt = ds.Tables["data"];

                if (dt.Rows.Count == 0)
                {
                    data.DataTransaction.Success = true;
                }
                else
                {
                    data.DataTransaction.Success = false;
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("00000", ex.Message));
            }
            return data;
        }

        #region Roles
        public List<Role> GetMemberRoles(Member data)
        {
            List<Role> myReturn = new List<Role>();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMemberRoles(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach(DataRow dr in dt.Rows)
                {
                    Role item = new Role();

                    item.ID = DataConvertor.toString(dr, "RoleID");
                    item.Name = DataConvertor.toString(dr, "Name");
                    item.Description = DataConvertor.toString(dr, "Description");

                    myReturn.Add(item);
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11000", "An error occurred retrieving the member's roles."));
            }

            return myReturn;
        }
        #endregion

        #region Groups
        public List<Group> GetMemberGroups(Member data)
        {
            List<Group> myReturn = new List<Group>();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMemberGroups(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    Group item = new Group();

                    item.ID = DataConvertor.toString(dr, "GroupID");
                    item.Name = DataConvertor.toString(dr, "Name");
                    item.Description = DataConvertor.toString(dr, "Description");

                    myReturn.Add(item);
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11000", "An error occurred retrieving the member's groups."));
            }

            return myReturn;
        }

        #endregion

        #region ID Cards
        public List<IDCard> GetMemberIDCards(Member data)
        {
            List<IDCard> myReturn = new List<IDCard>();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMemberIDCards(data), "data");
                DataTable dt = ds.Tables["data"];

                foreach (DataRow dr in dt.Rows)
                {
                    IDCard item = new IDCard();

                    item.CardID = DataConvertor.toString(dr, "ID");
                    item.DateDeleted = DataConvertor.toDateTimeOffset(dr, "DateDeleted");
                    item.Expires = DataConvertor.toDateTimeOffset(dr, "Expires");
                    item.LastUsed = DataConvertor.toDateTimeOffset(dr, "LastUsed");
                    item.MemberID = DataConvertor.toString(dr, "MemberID");
                    if (item.DateDeleted != DateTimeOffset.MinValue) item.IsDeleted = true;
                    if ((item.Expires != DateTimeOffset.MinValue) && (item.Expires < DateTimeOffset.Now)) item.IsExpired = true;

                    myReturn.Add(item);
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11100", "An error occurred retrieving the member's ID Cards."));
            }

            return myReturn;
        }

        public IDCard GetMemberIDCard(IDCard data)
        {

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.GetMemberIDCard(data), "data");
                DataTable dt = ds.Tables["data"];

                if(dt.Rows.Count == 1)
                {
                    data.CardID = DataConvertor.toString(dt.Rows[0], "ID");
                    data.DateDeleted = DataConvertor.toDateTimeOffset(dt.Rows[0], "DateDeleted");
                    data.Expires = DataConvertor.toDateTimeOffset(dt.Rows[0], "Expires");
                    data.LastUsed = DataConvertor.toDateTimeOffset(dt.Rows[0], "LastUsed");
                    data.MemberID = DataConvertor.toString(dt.Rows[0], "MemberID");
                    if (data.DateDeleted != DateTimeOffset.MinValue) data.IsDeleted = true;
                    if ((data.Expires != DateTimeOffset.MinValue) && (data.Expires < DateTimeOffset.Now)) data.IsExpired = true;

                    //myReturn.Add(item);
                }
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11100", "An error occurred retrieving the member's ID Cards."));
            }

            return data;
        }

        public IDCard AddMemberIDCard(IDCard data)
        {
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.AddMemberIDCard(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.Success = true;
                
            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11200", "An error occurred adding the member's ID Card."));
            }

            return data;
        }

        public IDCard DeleteMemberIDCard(IDCard data)
        {
            if (data.DataTransaction == null) data.DataTransaction = new DataTransaction();

            try
            {
                DataSet ds = SQLCONN.RunSQL_DataSet(MembershipSQL.DeleteMemberIDCard(data), "data");
                DataTable dt = ds.Tables["data"];
                data.DataTransaction.Success = true;

            }
            catch (Exception ex)
            {
                data.DataTransaction.Success = false;
                if (data.DataTransaction.TransactionErrors == null) data.DataTransaction.TransactionErrors = new List<Error>();
                data.DataTransaction.TransactionErrors.Add(new Error("DAO11200", "An error occurred adding the member's ID Card."));
            }

            return data;
        }
        #endregion

    }
}
